import random

import openpyxl
import pymysql
import requests
import threading
import time
def ua():
    ua_pool = [

        "Mozilla/5.0 (Windows NT 6.2;en-US) AppleWebKit/537.32.36 (KHTML, live Gecko) Chrome/59.0.3047.101 Safari/537.32",

        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.117 Safari/537.36",

        "Mozilla/5.0 (iPhone; CPU iPhone OS 14_5 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 MicroMessenger/8.0.5(0x18000528) NetType/4G Language/zh_CN",

        "Mozilla/5.0 (Linux; Android 11; M2012K11AC Build/RKQ1.200826.002; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/77.0.3865.120 MQQBrowser/6.2 TBS/045613 Mobile Safari/537.36 MMWEBID/7803 MicroMessenger/8.0.3.1880(0x2800033D) Process/tools WeChat/arm64 Weixin NetType/WIFI Language/zh_CN ABI/arm64",

        "Mozilla/5.0 (Linux; Android 9; Mi Note 3 Build/PKQ1.181007.001; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/77.0.3865.120 MQQBrowser/6.2 TBS/045609 Mobile Safari/537.36 MMWEBID/4799 MicroMessenger/8.0.2.1860(0x2800023D) Process/tools WeChat/arm64 Weixin NetType/4G Language/zh_CN ABI/arm64",

        "Mozilla/5.0 (iPhone; CPU iPhone OS 14_5_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 MicroMessenger/8.0.5(0x18000528) NetType/4G Language/zh_CN",

        "Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)",

        "Mozilla/5.0 (Linux; Android 11; V1824A Build/RP1A.200720.012; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/77.0.3865.120 MQQBrowser/6.2 TBS/045611 Mobile Safari/537.36 MMWEBID/8305 MicroMessenger/8.0.2.1860(0x2800023D) Process/tools WeChat/arm64 Weixin NetType/WIFI Language/zh_CN ABI/arm64",

        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36",

        "Mozilla/5.0 (iPhone; CPU iPhone OS 14_5_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 MicroMessenger/8.0.3(0x1800032c) NetType/WIFI Language/zh_CN",

        "Mozilla/5.0 (Linux; U; Android 10; zh-cn; MI 9 Build/QKQ1.190825.002) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/71.0.3578.141 Mobile Safari/537.36 XiaoMi/MiuiBrowser/13.3.12",
    ]
    return ua_pool[random.randint(1, 10)]


def login():
    login_url = 'http://sw.eccang.com/default/index/login'
    headers = {
        # "Accept": "application/json, text/javascript, */*; q=0.01",
        "User-Agent": "{}".format(ua()),
        "Referer": "http://sw.eccang.com/default/index/logout?company_code=sw"
    }
    body = {
        "userName": "jinhuaitao",
        "userPass": "jht834719#",
        "companyCode": ""
    }
    try:
        res = requests.post(url=login_url, headers=headers, data=body)
        cookies = res.cookies.items()
        print(cookies)
        cookie = ''
        for name, value in cookies:
            cookie += '{0}={1};'.format(name, value)
        print('UM_distinctid=17c15b16b7275-02b55430879ff9-2343360-1fa400-17c15b16b7376d;' + cookie)
        return cookie
    except Exception as err:
        print('获取cookie失败：\n{0}'.format(err))





def run():
    headers = {
        'Cookie': '{}'.format(login()),
        "User-Agent": "{}".format(ua()),

    }
    data = {
        'E4': '',
        'category[]': '',
        'search_combination_type': '1',
        'product_sku': '',
        'search_sub_type': '1',
        'sub_product_sku': ''
    }
    # total
    url1 = 'http://sw.eccang.com/product/overseas-warehouse-combination-product/list/page/1/pageSize/20'
    res1 = requests.post(url1, headers=headers, data=data).json()
    page=res1["total"]
    url = f'http://sw.eccang.com/product/overseas-warehouse-combination-product/list/page/1/pageSize/{page}'
    res = requests.post(url, headers=headers, data=data).json()
    # wb = openpyxl.Workbook()
    # ws = wb.create_sheet('sku')
    # ws.cell(1, 1).value = f"产品组合sku"
    # ws.cell(1, 2).value = f"产品组合名称"
    # ws.cell(1, 3).value = f"子产品sku"
    # ws.cell(1, 4).value = f"子产品数量"
    # ws.cell(1, 5).value = f"子产品名称"
    list_ts=[]
    for i in res["data"]:
        product_sku1 = i["product_sku"]
        product_title1 = i["product_title"]
        sub_product = i["sub_product"]
        ts=threading.Thread(target=go_run, args=(sub_product,product_sku1,product_title1,))
        list_ts.append(ts)

    for i_ts1 in list_ts:
        i_ts1.start()  # 启动线程

    for i_ts in list_ts:
        i_ts.join()  # 关闭线程
def go_run(sub_product,product_sku1,product_title1):
    for j in range(len(sub_product)):
        product_title2 = sub_product[j]["product_title"]
        sub_product_sku2 = sub_product[j]["sub_product_sku"].split(' * ')[0]
        sub_product_sku_num = sub_product[j]["sub_product_sku"].split(' * ')[1]
        mysqlrun(product_sku1, product_title1, sub_product_sku2, sub_product_sku_num, product_title2)
        # ws.cell(j+1, 1).value = f"{product_sku1}"
        # ws.cell(j+1, 2).value = f"{product_title1}"
        # ws.cell(j+1, 3).value = f"{sub_product_sku2}"
        # ws.cell(j+1, 4).value = f"{sub_product_sku_num.replace(' ', '')}"
        # ws.cell(j+1, 5).value = f"{product_title2}"
        # wb.save('./sku.xlsx')
        print(product_sku1, product_title1, sub_product_sku2, sub_product_sku_num, product_title2)



def mysqlrun(product_sku1, product_title1, sub_product_sku2, sub_product_sku_num, product_title2):
    conn = pymysql.connect(host='14.29.212.205', port=8306, user='asinuser', password='asinuser_SW168',
                           database='sun_read', charset='utf8')
    cursor = conn.cursor()
    sql = 'insert into det_overseas_combine(concat_sku, concat_name, son_sku, son_num, son_name) values(%s, %s, %s, %s, %s)'

    cursor.execute(sql, (product_sku1, product_title1, sub_product_sku2, sub_product_sku_num.replace(' ', ''), product_title2))

    conn.commit()
if __name__ == '__main__':
    # while True:
    #     newtime = time.strftime("%H:%M:%S", time.localtime())
    #     if newtime in ['08:30:00']:
    #         run()
    #         print('任务执行已完成')
    run()

